package com.aizu.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import com.aizu.entity.User;
import com.isp.jdbc.model.Pager;
import com.isp.utils.JdbcUtils;

@Service
public class RenterService extends CommonService {

	private final static String TABLE_TWO_CARD_GRANT = "t_two_card_grant";

	/**
	 * 转换成登陆对像
	 * 
	 * @param renterList
	 * @return
	 */
	private User convertUser(List<Map<String, Object>> renterList) {
		if (null != renterList && renterList.size() > 0) {
			Map<String, Object> renterMap = renterList.get(0);
			User renter = new User();
			renter.setUserId((Integer) renterMap.get("id"));
			renter.setUserName((String) renterMap.get("namel"));
			renter.setUserPhone((String) renterMap.get("phone"));
			renter.setUserType(User.USER_TYPE_RENTER);
			renter.setFromUserName((String) renterMap.get("wx_userid"));
			// renter.setLastLoginDate(null);
			return renter;
		}
		return null;
	}

	/**
	 * 获取用微信用户关联的手机号
	 * 
	 * @param fromUserName
	 * @return
	 * @throws SQLException
	 */
	public String findPhone(String fromUserName) throws SQLException {
		Map<String, Object> only = findOnly("SELECT tcg.phone FROM t_two_card_grant tcg WHERE tcg.wx_userid = ?",
				new Object[] { fromUserName });
		if (null != only)
			return (String) only.get("phone");
		return null;
	}

	/**
	 * 注册
	 * 
	 * @param map
	 * @return
	 */
	public User saveUser(Map<String, Object> map) throws Exception {
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT, TABLE_TWO_CARD_GRANT,
				map, null);
		boolean execute = this.execute((String) createSqlParam.get(JdbcUtils.SQL),
				(Object[]) createSqlParam.get(JdbcUtils.PARAM));
		User renter = null;
		if (execute) {
			// map.remove("create_date");
			// map.remove("mod_date");
			List<Map<String, Object>> renterList = findRenter(map);
			renter = convertUser(renterList);
		}
		return renter;
	}

	/**
	 * 绑定微信id
	 * 
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public boolean updateUser(User user) throws Exception {
		return this.execute("update " + TABLE_TWO_CARD_GRANT + " set wx_userid = ?, phone = ? where id = ?",
				new Object[] { user.getFromUserName(), user.getUserPhone(), user.getUserId() });
	}

	/**
	 * 绑定微信id
	 * 
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public boolean updateEnote(Map<String, Object> renter) throws Exception {
		return this.execute("update " + TABLE_TWO_CARD_GRANT + " set enote = ? where id = ?",
				new Object[] { renter.get("enote"), renter.get("id") });
	}

	/**
	 * 绑定微信id
	 * 
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public boolean clearWxId(Map<String, Object> map) throws Exception {
		return this.execute("update " + TABLE_TWO_CARD_GRANT + " set wx_userid = NULL where wx_userid = ?",
				new Object[] { map.get("wx_userid") });
	}

	/**
	 * 登陆
	 * 
	 * @param map
	 * @return
	 */
	public User loadUser(Map<String, Object> map) throws Exception {
		List<Map<String, Object>> renterList = findRenter(map);
		return convertUser(renterList);
	}

	/**
	 * 查找房客
	 * 
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> findRenter(Map<String, Object> map) throws Exception {
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, TABLE_TWO_CARD_GRANT,
				null, map);
		return this.find((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查找所有小区
	 * 
	 * @param map
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public Pager findAllCommunity(Map<String, Object> map, int pageNo, int pageSize) throws SQLException {
		StringBuilder sql = new StringBuilder(
				"SELECT d.name dname, c.* FROM t_community c, t_department d WHERE c.dept_id = d.id");
		List<Object> param = new ArrayList<Object>();

		if (null != map) {
			String community_id = (String) map.get("id");
			if (null != community_id) {
				param.add(community_id);
				sql.append(" AND c.id = ?");
			}
			String dept_id = (String) map.get("dept_id");
			if (null != dept_id) {
				param.add(dept_id);
				sql.append(" AND c.dept_id = ?");
			}
			String street_id = (String) map.get("street_id");
			if (null != street_id) {
				param.add(street_id);
				sql.append(" AND d.street_id = ?");
			}
			String area = (String) map.get("area");
			if (null != area) {
				param.add(area);
				sql.append(" AND d.area = ?");
			}
			String city = (String) map.get("city");
			if (null != city) {
				param.add(city);
				sql.append(" AND d.area IN (SELECT a.area_id FROM t_area a WHERE a.prent_id = ?)");
			}

			String name = (String) map.get("name");
			String dname = (String) map.get("dname");
			if (null != name || null != dname) {
				sql.append(" and (");
				if (null != name) {
					param.add(name);
					sql.append("c.name like ?");
					if (null != dname) {
						sql.append(" or ");
					}
				}
				if (null != dname) {
					param.add(dname);
					sql.append("d.name like ?");
				}
				sql.append(")");
			}
		}
		return findPaging(sql.toString(), param.toArray(), pageNo, pageSize);
	}

	/**
	 * 查找房子下的房间
	 * 
	 * @param communityId
	 * @return
	 */
	public List<Map<String, Object>> findRoomByCommunityId(int communityId) throws Exception {
		return this.find("SELECT r.*, (SELECT COUNT(1) FROM t_rent_bed rb WHERE rb.room_id = r.id) bed_count,"
				+ " (SELECT COUNT(1) FROM t_rent_bed rb WHERE rb.room_id = r.id AND rb.grant_id IS NULL) bed_surplus_count"
				+ " FROM t_rent_room r WHERE r.community_id = ?", new Object[] { communityId });
	}

	/**
	 * 查找房子下的房间
	 * 
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> findRoom(Map<String, Object> condition) throws Exception {
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, "t_rent_room", null,
				condition);
		return this.findOnly((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查找房间下的床位
	 * 
	 * @param roomId
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findBedByRoomId(int roomId) throws SQLException {
		return this.find(
				"SELECT rb.*, tcg.nameL grant_name FROM t_rent_bed rb LEFT JOIN t_two_card_grant tcg ON rb.grant_id = tcg.id WHERE rb.room_id = ?",
				new Object[] { roomId });
	}

	/**
	 * 查找床位
	 * 
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> findBed(Map<String, Object> condition) throws Exception {
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, "t_rent_bed", null,
				condition);
		return this.findOnly((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 下单
	 * 
	 * @param map
	 * @return
	 */
	public boolean saveOrder(Map<String, Object> map) throws Exception {
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT, "t_order", map, null);
		return this.execute((String) createSqlParam.get(JdbcUtils.SQL), (Object[]) createSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查找所有小区
	 * 
	 * @param map
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public Pager findMyOrder(Map<String, Object> map, int pageNo, int pageSize) throws SQLException {
		StringBuilder sql = new StringBuilder(
				"SELECT o.*, rm.NAME main_name FROM t_order o LEFT JOIN t_rent_main rm ON o.main_id = rm.ID WHERE 1 = 1");
		List<Object> param = new ArrayList<Object>();

		if (null != map) {
			Integer grant_id = (Integer) map.get("grant_id");
			if (null != grant_id) {
				param.add(grant_id);
				sql.append(" and o.grant_id = ?");
			}
		}
		sql.append(" order by o.create_date desc");
		return findPaging(sql.toString(), param.toArray(), pageNo, pageSize);
	}

	/**
	 * 更新订单状态
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public boolean updateMyOrder(Map<String, Object> data, Map<String, Object> condition) throws SQLException {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE, "t_order", data,
				condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 更新床位状态
	 * 
	 * @param data
	 * @return
	 * @throws Exception
	 */
	public boolean updateBedState(Map<String, Object> data) throws Exception {
		StringBuilder sb = new StringBuilder("update t_rent_bed set is_rent = ?, grant_id = ? where room_id = ?");
		List<Object> param = new ArrayList<Object>();
		param.add(data.get("is_rent"));
		param.add(data.get("grant_id"));
		param.add(data.get("room_id"));
		if (StringUtils.isNotBlank((String) data.get("id"))) {
			sb.append(" and id = ?");
			param.add(data.get("id"));
		}
		return this.execute(sb.toString(), param.toArray());
	}

	/**
	 * 查找个人基本信息
	 * 
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> findPersonalInformation(Map<String, Object> condition) throws Exception {
		Map<String, Object> personalQuerySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY,
				"t_information_personal", null, condition);
		return this.findOnly((String) personalQuerySqlParam.get(JdbcUtils.SQL),
				(Object[]) personalQuerySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 新增个人基本信息
	 * 
	 * @param map
	 * @return
	 */
	public long savePersonalInformation(Map<String, Object> map) throws Exception {
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT,
				"t_information_personal", map, null);
		return this.executeSave((String) createSqlParam.get(JdbcUtils.SQL),
				(Object[]) createSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 更新个人基本信息
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public boolean updatePersonalInformation(Map<String, Object> data, Map<String, Object> condition)
			throws SQLException {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE,
				"t_information_personal", data, condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查找居住信息
	 * 
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> findResidenceInformation(Map<String, Object> condition) throws Exception {
		Map<String, Object> residenceQuerySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY,
				"t_information_residence", null, condition);
		return this.findOnly((String) residenceQuerySqlParam.get(JdbcUtils.SQL),
				(Object[]) residenceQuerySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 新增居住信息
	 * 
	 * @param map
	 * @return
	 */
	public long saveResidenceInformation(Map<String, Object> map) throws Exception {
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT,
				"t_information_residence", map, null);
		return this.executeSave((String) createSqlParam.get(JdbcUtils.SQL),
				(Object[]) createSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 更新居住信息
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public boolean updateResidenceInformation(Map<String, Object> data, Map<String, Object> condition)
			throws SQLException {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE,
				"t_information_residence", data, condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查找就业计生信息
	 * 
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> findCareerBreedingInformation(Map<String, Object> condition) throws Exception {
		Map<String, Object> careerBreedingQuerySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY,
				"t_information_career_breeding", null, condition);
		return this.findOnly((String) careerBreedingQuerySqlParam.get(JdbcUtils.SQL),
				(Object[]) careerBreedingQuerySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 新增就业计生信息
	 * 
	 * @param map
	 * @return
	 */
	public long saveCareerBreedingInformation(Map<String, Object> map) throws Exception {
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT,
				"t_information_career_breeding", map, null);
		return this.executeSave((String) createSqlParam.get(JdbcUtils.SQL),
				(Object[]) createSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 更新就业计生信息
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public boolean updateCareerBreedingInformation(Map<String, Object> data, Map<String, Object> condition)
			throws SQLException {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE,
				"t_information_career_breeding", data, condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查找主要关系人信息
	 * 
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> findRelatedInformation(Map<String, Object> condition) throws Exception {
		Map<String, Object> relatedQuerySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY,
				"t_information_related", null, condition);
		return this.findOnly((String) relatedQuerySqlParam.get(JdbcUtils.SQL),
				(Object[]) relatedQuerySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 新增主要关系人信息
	 * 
	 * @param map
	 * @return
	 */
	public long saveRelatedInformation(Map<String, Object> map) throws Exception {
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT, "t_information_related",
				map, null);
		return this.executeSave((String) createSqlParam.get(JdbcUtils.SQL),
				(Object[]) createSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 更新主要关系人信息
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public boolean updateRelatedInformation(Map<String, Object> data, Map<String, Object> condition)
			throws SQLException {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE, "t_information_related",
				data, condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 修改手机号
	 * 
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public boolean updateUserPhone(String phone, String weixinId) throws Exception {
		return this.execute("update " + TABLE_TWO_CARD_GRANT + " set phone = ? where wx_userid = ?",
				new Object[] { phone, weixinId });
	}

	public boolean clearUserRoom(Map<String, Object> map) throws SQLException {
		return this.execute("update " + TABLE_TWO_CARD_GRANT + " set rentId = NULL where id = ?",
				new Object[] { map.get("grantId") });
	}
	
	/**
	 * 修改人员信息
	 * 
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public boolean updGrant(Map<String, Object> map) throws Exception {
		return this.execute("update " + TABLE_TWO_CARD_GRANT + " set rentId = ?, end_date = ? where id = ?",
				new Object[] { map.get("roomId"), map.get("endDate"), map.get("grantId") });
	}
}
